﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WebsiteBanThucAn.Models;
using System.Data;
using WebsiteBanThucAn.DAL;

namespace DAL
{
    public class MonAnDAL
    {
        private static void GanDoiTuong(MonAnModel mam, DataRow dr)
        {// Gán từng thuộc tính cho đối tượng
            mam.MonanID = int.Parse(dr[MonAnModel.monanid].ToString());
            mam.Tenmonan = dr[MonAnModel.tenmonan].ToString();
            mam.Gia = float.Parse(dr[MonAnModel.gia].ToString());
            mam.LoaimonanID = int.Parse(dr[MonAnModel.loaimonanid].ToString());
            mam.Mota = dr[MonAnModel.mota].ToString();
            mam.Hinhmonan = dr[MonAnModel.hinhmonan].ToString();
            mam.Ngaycapnhat = DateTime.Parse(dr[MonAnModel.ngaycapnhat].ToString());
            mam.Solanxem = int.Parse(dr[MonAnModel.solanxem].ToString());
            mam.Soluong = int.Parse(dr[MonAnModel.soluong].ToString());
            mam.Hethang = bool.Parse(dr[MonAnModel.hethang].ToString());
        }

        public static List<MonAnModel> LayDSMonAnTatCa()
        {// Lấy danh sách tất cả món ăn
            List<MonAnModel> list = new List<MonAnModel>();
            try
            {
                string sql = string.Format("select * from {0}", MonAnModel.monan);
                DataTable dt = SqlDataProvider.DocBang(sql);
                foreach (DataRow dr in dt.Rows)
                {
                    MonAnModel mam = new MonAnModel();
                    GanDoiTuong(mam, dr);
                    list.Add(mam);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public static List<MonAnModel> LayDSMonAnTheoLoaiMonAnID(int id)
        {// Lấy danh sách tất cả món ăn theo ID của loại món ăn
            List<MonAnModel> list = new List<MonAnModel>();
            try
            {
                string sql = string.Format("select * from {0} where {1}='{2}'", MonAnModel.monan, MonAnModel.loaimonanid, id);
                DataTable dt = SqlDataProvider.DocBang(sql);
                foreach (DataRow dr in dt.Rows)
                {
                    MonAnModel mam = new MonAnModel();
                    GanDoiTuong(mam, dr);
                    list.Add(mam);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public static MonAnModel LayThongTinMonAnTheoID(int id)
        {// Lấy thông tin món ăn theo ID
            MonAnModel mam = new MonAnModel();
            try
            {
                string sql = string.Format("select * from {0} where {1}='{2}'", MonAnModel.monan, MonAnModel.monanid, id);
                DataTable dt = SqlDataProvider.DocBang(sql);
                foreach (DataRow dr in dt.Rows)
                {
                    GanDoiTuong(mam, dr);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return mam;
        }

        public static List<MonAnModel> LayDSMonAnTheoTraCuu(string search)
        {// Lấy danh sách món ăn theo tra cứu
            List<MonAnModel> list = new List<MonAnModel>();
            try
            {
                string sql = string.Format("select * from {0} where", MonAnModel.monan);
                DataTable dt = SqlDataProvider.DocBang(sql);
                foreach (DataRow dr in dt.Rows)
                {
                    MonAnModel mam = new MonAnModel();
                    GanDoiTuong(mam, dr);
                    list.Add(mam);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public static bool ThemMonAn(MonAnModel mam)
        {
            bool result = false;
            try
            {
                string sql = string.Format("insert into {0} values ", MonAnModel.monan);
                sql += string.Format("(N'{0}','{1}','{2}',N'{3}',N'{4}',convert(date,'{5}',101),'{6}','{7}','{8}')",
                    mam.Tenmonan, mam.Gia, mam.LoaimonanID, mam.Mota, mam.Hinhmonan, mam.Ngaycapnhat, mam.Solanxem, mam.Soluong, mam.Hethang);
                int n = SqlDataProvider.ThucHienLenh(sql);
                if (n == 1)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool XoaMonAn(int id)
        {
            bool result = false;
            try
            {
                string sql = string.Format("delete {0} where {1}='{2}'", MonAnModel.monan, MonAnModel.monanid, id);
                int n = SqlDataProvider.ThucHienLenh(sql);
                if (n == 1)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool SuaMonAn(MonAnModel mam)
        {
            bool result = false;
            try
            {
                string sql = string.Format("update {0} set ", MonAnModel.monan);
                sql += string.Format("{0}=N'{1}',{2}='{3}',{4}='{5}',{6}=N'{7}',{8}=N'{9}',{10}=convert(date,'{11}',101),{12}='{13}',{14}='{15}',{16}='{17}'",
                    MonAnModel.tenmonan,
                    mam.Tenmonan,
                    MonAnModel.gia,
                    mam.Gia,
                    MonAnModel.loaimonanid,
                    mam.LoaimonanID,
                    MonAnModel.mota,
                    mam.Mota,
                    MonAnModel.hinhmonan,
                    mam.Hinhmonan,
                    MonAnModel.ngaycapnhat,
                    mam.Ngaycapnhat,
                    MonAnModel.solanxem,
                    mam.Solanxem,
                    MonAnModel.soluong,
                    mam.Soluong,
                    MonAnModel.hethang,
                    mam.Hethang);
                sql += string.Format(" where {0}='{1}'", MonAnModel.monanid, mam.MonanID);
                int n = SqlDataProvider.ThucHienLenh(sql);
                if (n == 1)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
    }
}
